"""
用户评分评论数据库操作类
提供用户评分评论的批量插入、更新和查询操作
"""

import time
import aiomysql
from typing import List, Dict, Any, Optional
from db.operations import BaseOperations
from db import DatabaseConnection, db_connection


class UserRatingsCommentsOperations(BaseOperations):
    """用户评分评论数据库操作类"""

    def __init__(self):
        # 继承BaseOperations，指定表名为user_ratings_comments
        super().__init__("user_ratings_comments")
        self.db_connection: DatabaseConnection = db_connection

    async def batch_upsert_ratings_comments(
        self, ratings_comments_data: List[Dict[str, Any]]
    ) -> int:
        """
        批量插入/更新用户评分评论数据
        如果记录不存在就插入，如果存在就更新

        Args:
            ratings_comments_data (List[Dict[str, Any]]): 评分评论数据列表
            每个字典应包含评分评论的字段，例如：
            {
                "user_id": 1,
                "book_id": 123,
                "rating": 4.5,  # 评分
                "comment": "这本书很好看",  # 评论内容
                "created_at": 1686825600,  # 创建时间戳
                "updated_at": 1686825600   # 更新时间戳
            }

        Returns:
            int: 成功插入/更新的记录数量
        """
        if not ratings_comments_data:
            return 0

        # 验证必要字段
        valid_data = []
        for data in ratings_comments_data:
            if "user_id" not in data or "book_id" not in data or "rating" not in data:
                print(
                    "警告：数据中缺少必要字段(user_id, book_id, rating)，跳过该条记录"
                )
                continue
            valid_data.append(data.copy())  # 创建副本避免修改原数据

        if not valid_data:
            return 0

        # 调用父类的批量插入方法，使用update策略处理重复键
        return await self.batch_insert(valid_data, on_duplicate="update")

    async def batch_query_ratings_comments(
        self, user_book_pairs: List[Dict[str, int]] = None
    ) -> List[Dict[str, Any]]:
        """
        批量查询用户评分评论记录
        可以根据用户ID和图书ID列表进行查询

        Args:
            user_book_pairs (List[Dict[str, int]]): 用户ID和图书ID对列表
            每个字典应包含：
            {
                "user_id": 1,
                "book_id": 123
            }
        Returns:
            List[Dict[str, Any]]: 评分评论记录列表
            每个字典包含：
            {
                "user_id": 1,
                "book_id": 123,
                "rating": 4.5,
                "comment": "这本书很好看",
                "created_at": 1686825600,
                "updated_at": 1686825600
            }
        """
        if not user_book_pairs:
            return []

        # 构建OR条件组合
        or_conditions = []
        params = []

        # 添加特定用户-图书对的条件
        for pair in user_book_pairs:
            user_id = pair.get("user_id")
            book_id = pair.get("book_id")
            if user_id is not None and book_id is not None:
                or_conditions.append("(user_id = %s AND book_id = %s)")
                params.extend([user_id, book_id])

        if not or_conditions:
            return []

        where_clause = " OR ".join(or_conditions)
        order_by = "updated_at DESC"

        return await self.batch_query(
            where_clause=where_clause, params=tuple(params), order_by=order_by
        )

    async def batch_delete_ratings_comments(
        self, user_book_pairs: List[Dict[str, int]]
    ) -> int:
        """
        批量删除用户评分评论记录
        根据用户ID和图书ID对进行删除

        Args:
            user_book_pairs (List[Dict[str, int]]): 用户ID和图书ID对列表
            每个字典应包含：
            {
                "user_id": 1,
                "book_id": 123
            }
        Returns:
            int: 成功删除的记录数量
        """
        if not user_book_pairs:
            return 0

        # 构建OR条件组合
        or_conditions = []
        params = []

        # 添加特定用户-图书对的条件
        for pair in user_book_pairs:
            user_id = pair.get("user_id")
            book_id = pair.get("book_id")
            if user_id is not None and book_id is not None:
                or_conditions.append("(user_id = %s AND book_id = %s)")
                params.extend([user_id, book_id])

        if not or_conditions:
            return 0

        where_clause = " OR ".join(or_conditions)

        return await self.batch_delete(where_clause=where_clause, params=tuple(params))

    async def get_book_ratings_comments_with_pagination(
        self,
        book_id: int,
        page: int = 1,
        limit: int = 10,
        order_by: str = "updated_at DESC",
    ) -> tuple[int, List[Dict[str, Any]]]:
        """
        分页获取指定书籍下的评分评论，按时间顺序
        使用原生SQL实现高性能查询

        Args:
            book_id: 图书ID
            page: 页码，从1开始
            limit: 每页数量
            order_by: 排序方式，默认按更新时间降序排列

        Returns:
            tuple[int, List[Dict[str, Any]]]: 返回总数和评分评论列表
            - int: 总评分评论数量
            - List[Dict[str, Any]]: 评分评论列表，每个字典包含评分评论信息
            每个字典包含：
            {
                "user_id": 1,
                "book_id": 123,
                "rating": 4.5,
                "comment": "这本书很好看",
                "created_at": 1686825600,
                "updated_at": 1686825600
            }
        """
        offset = (page - 1) * limit  # 查询评分评论详细信息的SQL，按指定顺序排列
        sql = """
        SELECT *
        FROM user_ratings_comments
        WHERE book_id = %s
        ORDER BY {order_by}
        LIMIT %s OFFSET %s
        """.format(
            order_by=order_by
        )

        # 查询总数的SQL
        count_sql = """
        SELECT COUNT(*) as total
        FROM user_ratings_comments
        WHERE book_id = %s
        """

        pool = await self.db_connection.get_pool()

        try:
            async with pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    # 先查询总数
                    await cursor.execute(count_sql, (book_id,))
                    count_result = await cursor.fetchone()
                    total = count_result["total"] if count_result else 0

                    # 查询评分评论数据
                    await cursor.execute(sql, (book_id, limit, offset))
                    ratings_comments = await cursor.fetchall()

                    return total, ratings_comments if ratings_comments else []

        except Exception as e:
            print(f"查询图书评分评论失败: {e}")
            return 0, []
